package code20_jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Test02_数据库操作 {
	public static void main(String[] args) {
		// String sql = "delete from t_user where username='aaa'";
		// String sql =
		// "update t_user set username='zhangsan' , password='111' where id=2";
		// String sql = "create table t_wbs22032(id int)";
//		String sql = "drop database itany";
//		update(sql);

		// 严禁写select *，原因：1.效率低 2.可读性 3.可能会导致错误
		String sql = "select id,username,password,age from t_user";
		List<User> users = query(sql);
		for(User u : users){
			System.out.println(u);
		}
	}

	/*
	 * 更新操作 executeUpdate 适用于:insert/delete/update/create/drop
	 */
	public static void update(String sql) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = JdbcUtil.getConnection();
			stmt = conn.createStatement();
			int num = stmt.executeUpdate(sql);
			System.out.println(num); // 对于更新操作，结果一般无需处理
		}catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stmt, null);
		}
	}

	/*
	 * 查询操作 executeQuery
	 */
	public static List<User> query(String sql) {
		List<User> users = new ArrayList<User>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = JdbcUtil.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			// 读取ResultSet中的数据
			while(rs.next()){ // 作用：1.指向下一条数据 2.返回下一条数据是否存在
				int id = rs.getInt(1); // 根据列的编号来获取，从1开始
				String username = rs.getString(2);
				String password = rs.getString("password"); // 根据列名来获取
				int age = rs.getInt("age");
				// System.out.println(id+"，"+username+"，"+password+"，"+age);
				User user = new User(id, username, password, age);
				users.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(conn, stmt, rs);
		}
		return users;
	}

}
